using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Linq;

namespace Infrastucture
{
    public class MutasiHutangRepository : BaseNHibernateRepository<MutasiHutang>
    {
        public MutasiHutangRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {

        }


        public void DeleteMutasiHutang(string ReferenceNumber)
        {

            ISQLQuery command =
                Session.CreateSQLQuery("Delete from MutasiHutang Where ReferenceNumber='" + ReferenceNumber + "'");
            int res = command.ExecuteUpdate();
        }

        public void DeleteMutasiHutangByCoa(string coa)
        {

            ISQLQuery command =
                Session.CreateSQLQuery("Delete from MutasiHutang Where Coa='" + coa + "'");
            int res = command.ExecuteUpdate();
        }


        public MutasiHutang FindLastMutasi(string supplierId, string invoiceNo, DateTime tglMutasi)
        {
            var  mutasiHutang =
                Session.Linq<MutasiHutang>().Where(x => x.SupplierId == supplierId && x.InvoiceNo == invoiceNo && x.Tanggal <= tglMutasi)
                    .OrderByDescending(
                        x => x.Tanggal).FirstOrDefault();
            if (mutasiHutang == null)
                mutasiHutang = new MutasiHutang();
            return mutasiHutang;
        }


        public List<MutasiHutang> GetHutangBelumLunas()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM MutasiHutang mp  ");
            sb.Append("WHERE mp.Tanggal=(SELECT MAX(Tanggal) FROM MutasiHutang mp2 WHERE mp2.SupplierId = mp.SupplierId ");
            sb.Append("AND mp2.InvoiceNo = mp.InvoiceNo) AND mp.SaldoSisa > 0 ");
            var query = Session.CreateSQLQuery(sb.ToString()).AddEntity(typeof(MutasiHutang));
            return query.List<MutasiHutang>().ToList();
        }

        public List<MutasiHutang> GetHutangBelumLunas(string SupplierId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM MutasiHutang mp  ");
            sb.Append("WHERE mp.Tanggal=(SELECT MAX(Tanggal) FROM MutasiHutang mp2 WHERE mp2.SupplierId = mp.SupplierId ");
            sb.AppendFormat("AND mp2.InvoiceNo = mp.InvoiceNo) AND mp.SaldoSisa > 0 AND mp.SupplierId='{0}' ", SupplierId);
            var query = Session.CreateSQLQuery(sb.ToString()).AddEntity(typeof(MutasiHutang));
            return query.List<MutasiHutang>().ToList();
        }  
     
    }
}
